package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.ResponseBody;

import com.sq.partner.manager.dao.RuleAgentConfigDao;
import com.sq.partner.manager.mybatis.model.RuleAgent;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;
@Repository
public class RuleAgentConfigDaoImpl extends BaseDaoImpl implements RuleAgentConfigDao {
	
	@Override
	public Page<Map<String, Object>> queryRuleAgentPageByCriteria(
			Page<Map<String, Object>> page) throws Exception {
	Map<String,Object> paramMap = page.getMap();
		int total = (Integer) this.query("queryRuleAgentCountByFilter", paramMap);
		page.setTotal(total);
		List<Map<String,Object>> resultList = this.queryList("queryRuleAgentByFilter", paramMap);
		page.setData(resultList);
		return page;
	}
	@Override
	public Page<Map<String, Object>> queryRulePageByCriteria(
			Page<Map<String, Object>> page) throws Exception {
		Map<String,Object> paramMap = page.getMap();
		int total = (Integer) this.query("queryRuleCountByFilter", paramMap);
		page.setTotal(total);
		List<Map<String,Object>> resultList = this.queryList("queryRuleByFilter", paramMap);
		page.setData(resultList);
		return page;
	}

	@Override
	public boolean deleRuleAgentById(String ruleAgentId) throws Exception {
		 return this.delete("deleteRuleAgentById", ruleAgentId==null?"":ruleAgentId);
	}
	@Override
	public boolean addRuleAgentById(RuleAgent ruleAgent) throws Exception {
		return this.add("insertRuleAgent", ruleAgent==null?"":ruleAgent);
	}
	@Override
	public boolean updateRuleAgent(RuleAgent ruleAgent) throws Exception {
		return this.add("updateRuleAgent", ruleAgent==null?"":ruleAgent);
	}
	@Override                                      
	public Map<String,Object> queryRuleAgentById(String ruleAgentId) throws Exception {
		return (Map<String, Object>) this.query("queryRuleAgentById",ruleAgentId);
	}
	@Override
	public Page<Map<String, Object>> queryAgentInfoPageByPlanNo(
			Page<Map<String, Object>> page) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			Map<String,Object> paramMap = page.getMap();
			sqlbuf.append(" select t.id, t.user_code, u.user_name, t.dpt_cde, t.service_code,t.service_name,t.C_BRKR_NME,t.C_OPR_CDE,t.emp_no,t.emp_name,t.card_applicant, t.team_code, ")
		          .append(" d1.code_name as channlecodedes,d2.code_name as channletypedes,d3.code_name as newbsnstypedes, t.agent_no, t.protocol_no")
		          .append("  , rela.insu_plan_no as planNO from T_TRAD_AGENT_INFO t  ")
		          .append(" left join T_FACADE_USER u on t.user_code = u.user_code ")
		          .append(" left join T_GATEWAY_DICT d1 on d1.code_type ='belong_to_channel' and t.channle_code = d1.code ")
			      .append(" left join T_GATEWAY_DICT d2 on d2.code_type ='channel_type' and t.channle_type = d2.code ")
				  .append(" left join T_GATEWAY_DICT d3 on d3.code_type ='new_business_source' and t.new_bsns_type = d3.code ")
				  .append(" left join T_TRAD_AGENT_INFO_RELA rela  on t.id=rela.agent_info_id  ")
				  .append(" WHERE t.id not in (select r.agent_id from t_rule_agent r where r.rule_id= '"+paramMap.get("ruleId")+"') ");
						
			String sql;
			String planNo =StrUtil.getVal(paramMap.get("planNo"));
			if(StrUtil.isNotEmpty(planNo)){
				sqlbuf.append(" and rela.insu_plan_no = ?");
				list.add(planNo);
				islike.add(false);
			}
			String agentType =StrUtil.getVal(paramMap.get("agentType"));
			if(StrUtil.isNotEmpty(agentType)){
				sqlbuf.append(" and t.agent_type = ?");
				list.add(agentType);
				islike.add(false);
			}
			String dept_code = StrUtil.getVal(paramMap.get("dept_code"));
			if(StrUtil.isNotEmpty(dept_code) && !"00".equals(dept_code)){
			  sqlbuf.append(" and t.dpt_cde like ?");
			  list.add(dept_code+"%");
			  islike.add(false);
			}else if(StrUtil.isNotEmpty(dept_code) && "00".equals(dept_code)){
				sqlbuf.append(" and t.dpt_cde in (select dept_code from t_core_dept t connect by prior dept_code = snr_dept start with dept_code in ( ? ))");
				list.add(dept_code);
				islike.add(false);
			}
			/*sqlbuf.append("ORDER BY planNO ");*/
			sql=sqlbuf.toString();
			sql = "SELECT * FROM ("+sql+") A WHERE 1=1 ";
			
		/*	sql = sql +" ORDER BY A.user_code ";*/
			sql = sql +" ORDER BY planNO ,A.user_code ";
			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List resultList = DBUtil.transRs2List(rs);
			page.setData(resultList);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}

		return page;
	}
	@Override
	public List<Map<String, Object>> queryRuleAgentByRuleId(String ruleAgentId)
			throws Exception {
		
		return  (List<Map<String, Object>>) this.queryList("queryRuleAgentByRuleId",ruleAgentId);
	}

}
